package com.onion.codegen.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import com.onion.codegen.bean.DbTableProperties;
import com.onion.codegen.db.DbProperties;


public class DbMetaDataUtil {

	public static DbProperties dbProperties;

	private static final String SQL = "SELECT * FROM ";// 数据库操作
	
	private static final String TABLE_REMARK_SQL = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.tables where Table_name=";// 数据库操作

	private static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(dbProperties.getDriver());
			conn = DriverManager.getConnection(dbProperties.getUrl(),
					dbProperties.getUserName(), dbProperties.getPassWord());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	private static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取数据库下的所有表名
	 */
	public static List<String> getTableNames() {
		List<String> tableNames = new ArrayList<>();
		Connection conn = getConnection();
		ResultSet rs = null;
		try {
			// 获取数据库的元数据
			DatabaseMetaData db = conn.getMetaData();
			// 从元数据中获取到所有的表名
			rs = db.getTables(conn.getCatalog(), conn.getCatalog(), "%", new String[] { "TABLE" });
			while (rs.next()) {
				tableNames.add(rs.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				closeConnection(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tableNames;
	}

	/**
	 * 获取表中所有字段名称
	 * 
	 * @param tableName 表名
	 * @return
	 */
	public static List<String> getColumnNames(String tableName) {
		List<String> columnNames = new ArrayList<>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnNames.add(rsmd.getColumnName(i + 1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return columnNames;
	}

	/**
	 * 获取表中所有字段类型
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnTypes(String tableName) {
		List<String> columnTypes = new ArrayList<>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnTypes.add(rsmd.getColumnTypeName(i + 1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return columnTypes;
	}

	/**
	 * 获取表中字段的所有注释
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnComments(String tableName) {
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		List<String> columnComments = new ArrayList<>();// 列名注释集合
		ResultSet rs = null;
		try {
			pStemt = conn.prepareStatement(tableSql);
			rs = pStemt.executeQuery("show full columns from " + tableName);
			while (rs.next()) {
				columnComments.add(rs.getString("Comment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
					closeConnection(conn);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return columnComments;
	}

	public static List<DbTableProperties> initTableProperties(Set<String> genTables) throws Exception {
		List<String> tableNames = null;
//		if(genTables!=null) {
			tableNames = new ArrayList<String>(genTables);
//		}else {
			tableNames = getTableNames();
//		}
		List<DbTableProperties> genCodePropertiesList = new ArrayList<DbTableProperties>();
		for (String tableName : tableNames) {
			DbTableProperties dbTableProperties = new DbTableProperties();
			
			List<String> columnNames = getColumnNames(tableName);
			List<String> columnTypes = getColumnTypes(tableName);
			List<String> columnComments = getColumnComments(tableName);

			dbTableProperties.setTableName(tableName);
			dbTableProperties.setColumnNames(columnNames);
			dbTableProperties.setColumnTypes(columnTypes);
			dbTableProperties.setColumnComments(columnComments);

			genCodePropertiesList.add(dbTableProperties);
		}
		return genCodePropertiesList;
	}
	
	
	public static List<DbTableProperties> initTablePropertie(Set<String> genTables) throws Exception {
		List<String> tableNames = new ArrayList<>();
		List<DbTableProperties> genCodePropertiesList = new ArrayList<DbTableProperties>();
		Connection conn = getConnection();
		ResultSet rs = null;
		PreparedStatement pStemt = null;
		// 获取数据库的元数据
		DatabaseMetaData db = conn.getMetaData();
		if(genTables!=null&&genTables.size()>0) {
			tableNames.addAll(genTables);
		}else {
			// 从元数据中获取到所有的表名
			rs = db.getTables(conn.getCatalog(), conn.getCatalog(), "%", new String[] { "TABLE" });
			while (rs.next()) {
				tableNames.add(rs.getString(3));
			}
		}
		for(String tableName:tableNames) {
			List<String> columnNames = new ArrayList<>();
			List<String> columnTypes = new ArrayList<>();
			List<String> columnComments = new ArrayList<>();
			
			DbTableProperties dbTableProperties = new DbTableProperties();
			String tableSql = SQL + tableName;
			pStemt = conn.prepareStatement(tableSql);
			ResultSetMetaData rsmd = pStemt.getMetaData();
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnNames.add(rsmd.getColumnName(i + 1));
				columnTypes.add(rsmd.getColumnTypeName(i + 1));
			}
			rs = pStemt.executeQuery("show full columns from " + tableName);
			while (rs.next()) {
				columnComments.add(rs.getString("Comment"));
			}
			String tableRemarkSql = TABLE_REMARK_SQL + "'"+tableName+"'";
			rs = pStemt.executeQuery(tableRemarkSql);
			while (rs.next()) {
				dbTableProperties.setTableDescribe(rs.getString(2));
			}
			dbTableProperties.setTableName(tableName);
			dbTableProperties.setColumnNames(columnNames);
			dbTableProperties.setColumnTypes(columnTypes);
			dbTableProperties.setColumnComments(columnComments);
			genCodePropertiesList.add(dbTableProperties);
		}
		rs.close();
		closeConnection(conn);
		return genCodePropertiesList;
	}



	public static DbProperties getDbProperties() {
		return dbProperties;
	}

	public static void setDbProperties(DbProperties dbProperties) {
		DbMetaDataUtil.dbProperties = dbProperties;
	}

	public static String getSql() {
		return SQL;
	}


}